CREATE OR REPLACE PACKAGE LOTCOMBINE IS

--===============================================================
-- Package information
--===============================================================
FUNCTION Version RETURN VARCHAR2
;
--===============================================================
-- Type declarations
--===============================================================
TYPE TYPE_LotCombine_Lot IS RECORD
(
    ContainerName Container.ContainerName%TYPE
    , ContainerId Container.ContainerId%TYPE
    , CurrentStatusId Container.CurrentStatusId%TYPE
    , OwnerId Container.OwnerId%TYPE
    , ProductId Container.ProductId%TYPE
    , SpecObjectCategory Spec.ObjectCategory%TYPE
    , SpecObjectType Spec.ObjectType%TYPE
    , ProcessSpecId Container.ProcessSpecId%TYPE
    , ProcessSpecName A_ProcessSpecBase.ProcessSpecName%TYPE
    , ProcessSpecRevision A_ProcessSpec.ProcessSpecRevision%TYPE
    , OriginalContainerName Container.ContainerName%TYPE
    , LotType Container.LotType%TYPE
    , LotCategory Container.LotCategory%TYPE
    , MfgOrderID Container.MfgOrderID%TYPE
    , DateCode A_LotAttributes.DateCode%TYPE
    , OPN A_LotAttributes.OPN%TYPE
    , OPNRevision A_LotAttributes.OPNRevision%TYPE
    , LotOPN A_LotAttributes.OPN%TYPE
    , LotOPNRevision A_LotAttributes.OPNRevision%TYPE
    , BinNumber A_LotAttributes.BinNumber%TYPE
    , BucketNumber A_LotAttributes.BucketNumber%TYPE
    , Device Product.Device%TYPE
    , DeviceVersion Product.DeviceVersion%TYPE
    , PackageType Product.PackageType%TYPE
    , Leads Product.Leads%TYPE
    , AssemblyVersion Product.AssemblyVersion%TYPE
    , FabPlantArea Product.FabPlantArea%TYPE
    , ExternalDevice Product.ExternalDevice%TYPE
    , ProductOPN Product.OPN%TYPE
    , ProductOPNRevision Product.OPNRevision%TYPE
    , MaskOption Product.MaskOption%TYPE
/*  Mdify Date:2011-08-26: FinalTestSpecName is not in use in InSite 4.5    
    , FinalTestSpecName Product.FinalTestSpecName%TYPE
    , FinalTestSpecRevision Product.FinalTestSpecRevision%TYPE*/
    , FormatCode Product.FormatCode%TYPE
    , ProductLineName A_ProductLine.ProductLineName%TYPE
    , SpecName SpecBase.SpecName%TYPE
    --===============================
    -- Add 3 columns for check substrate info
    -- Deng Chao 2009-04-14
    --===============================
    , SubstrateVersion a_Lotattributes.Substraterev%TYPE
    , StripDrawingVersion a_Lotattributes.Stripdrawingrev%TYPE
    , SubstrateVendor a_Lotattributes.Substratevendor%TYPE
    --===============================
    -- Add 1 columns for check Future hold Setup
    -- Bryan 2010-03-30
    --===============================
    , FutureSetupCount integer
    , InprocessStatus VARCHAR2(40)
    , MTFirstStep specbase.specname%TYPE
)
;
TYPE COLL_LotCombine_Lot IS TABLE OF TYPE_LotCombine_Lot INDEX BY BINARY_INTEGER
;
TYPE TYPE_LotCombine_InputData IS RECORD
(
    MainLotId Container.ContainerName%TYPE
    , ChildLotIds TRGUtil.COLLECTION
)
;
TYPE TYPE_LotCombine_ServiceData IS RECORD
(
    MainLot TYPE_LotCombine_Lot
    , ChildLots COLL_LotCombine_Lot
    , NewDateCode A_LotAttributes.DateCode%TYPE := '-'
    , NewProductName ProductBase.ProductName%TYPE := '-'
    , NewBinNumber A_LotAttributes.BinNumber%TYPE := '0'
)
;
--===============================================================
-- Function and Procedure declarations
--===============================================================
FUNCTION VerifyData (MainLotId IN VARCHAR2
                     , ChildLotIds IN VARCHAR2)
RETURN VARCHAR2
;
PROCEDURE VerifyData2 (MainLotId IN VARCHAR2
                       , ChildLotIds IN VARCHAR2
                       , SuccessMessage IN OUT VARCHAR2)
;
FUNCTION ExecuteData (LotIds IN VARCHAR2)
RETURN VARCHAR2
;
PROCEDURE ExecuteData2 (LotIds IN VARCHAR2)
;
FUNCTION GetMainLot (LotIds IN VARCHAR2)
RETURN VARCHAR2
;
PROCEDURE VerifyAttributes (InputData IN TYPE_LotCombine_InputData
                            , ServiceData IN TYPE_LotCombine_ServiceData)
;
PROCEDURE VerifyDateCode (InputData IN TYPE_LotCombine_InputData
                          , ServiceData IN TYPE_LotCombine_ServiceData)
;
PROCEDURE SetAttributes (InputData IN TYPE_LotCombine_InputData
                         , ServiceData IN OUT TYPE_LotCombine_ServiceData)
;
--===========================================================================
-- End of package specification
--===========================================================================
END;
/
CREATE OR REPLACE PACKAGE BODY LOTCOMBINE AS

--=====================
-- Package information
--=====================
FUNCTION Version RETURN VARCHAR2 IS BEGIN RETURN ('3.4.0001'); END;

--=====================
-- VerifyData
--=====================
FUNCTION VerifyData (MainLotId IN VARCHAR2
                     , ChildLotIds IN VARCHAR2)
RETURN VARCHAR2 IS
    --=====================
    -- Input parameter variables
    --=====================

    --=====================
    -- Local variables
    --=====================
    vSuccessMessage VARCHAR2(122);
    --=====================
    -- Pragma Declarations
    --=====================
    eExit EXCEPTION;
    PRAGMA EXCEPTION_INIT (eExit, -20000);
    eAppError EXCEPTION;
    PRAGMA EXCEPTION_INIT (eAppError, -20005);
BEGIN
    --=====================
    -- Call the procedure
    --=====================
    VerifyData2 (MainLotId
                 , ChildLotIds
                 , vSuccessMessage);
    --=====================
    -- Return success
    --=====================
    RETURN (vSuccessMessage);
EXCEPTION
    WHEN eExit THEN
        RETURN ('LotCombine.VerifyData->Exited prematurely');
    WHEN eAppError THEN
        RETURN (REPLACE(SQLERRM, 'ORA-20005: '));
    WHEN OTHERS THEN
        RETURN ('LotCombine.VerifyData->' || SQLERRM);
END VerifyData;

--=====================
-- VerifyData2
--=====================
PROCEDURE VerifyData2 (MainLotId IN VARCHAR2
                       , ChildLotIds IN VARCHAR2
                       , SuccessMessage IN OUT VARCHAR2)
IS
    --=====================
    -- Input parameter variables
    --=====================

    --=====================
    -- Cursor Declarations
    --=====================
    CURSOR curLOT (IN_ContainerName Container.ContainerName%TYPE) IS
    SELECT
        A.ContainerName ContainerName
        , A.ContainerId ContainerId
        , A.CurrentStatusId CurrentStatusId
        , A.OwnerId OwnerId
        , A.ProductId ProductId
        , '' SpecObjectCategory
        , '' SpecObjectType
        , A.ProcessSpecId ProcessSpecId
        , '' ProcessSpecName
        , '' ProcessSpecRevision
        , SUBSTR(E.ContainerName, 1, INSTR(E.ContainerName, '.', 1) - 1) OriginalContainerName
        , A.LotType
        , A.LotCategory
	      --=================
        -- Add new criterion: check Work Order
        -- Deng Chao
        -- 2007-12-07
        --=================
	      , A.MfgOrderID
        , B.DateCode DateCode
        , '' OPN
        , '' OPNRevision
        , B.OPN LotOPN
        , B.OPNRevision LotOPNRevision
        , B.BinNumber BinNumber
        , B.BucketNumber BucketNumber
        , C.Device Device
        , C.DeviceVersion DeviceVersion
        , C.PackageType PackageType
        , C.Leads Leads
        , C.AssemblyVersion AssemblyVersion
        , C.FabPlantArea FabPlantArea
        , C.ExternalDevice ExternalDevice
        , C.OPN ProductOPN
        , C.OPNRevision ProductOPNRevision
        , C.MaskOption MaskOption
        /*  Mdify Date:2011-08-26 FinalTestSpecName is not in use in InSite 4.5    
        , C.FinalTestSpecName FinalTestSpecName
        , C.FinalTestSpecRevision FinalTestSpecRevision*/
        , C.FormatCode FormatCode
        , D.ProductLineName ProductLineName
        , H.SpecName
        --=================
        -- Add new criterion: check Substrate information
        -- Deng Chao
        -- 2009-04-14
        --=================
        , B.SUBSTRATEREV
        , B.STRIPDRAWINGREV
        , B.SUBSTRATEVENDOR
        , 0
        , '' InprocessStatus
        , '' MTFirstStep
    FROM
        Container A
        , A_LotAttributes B
        , Product C
        , A_ProductLine D
        , Container E
        , CurrentStatus F
        , Spec G
        , SpecBase H
    WHERE
        A.ContainerName = IN_ContainerName
        AND A.ContainerId = B.ContainerId
        AND A.ProductId = C.ProductId
        AND C.ProductLineId = D.ProductLineId
        AND E.ContainerId = A.ContainerId
        AND F.CurrentStatusId = A.CurrentStatusId
        AND G.SpecId = F.SpecId
        AND H.SpecBaseId = G.SpecBaseId
    ;
    
    CURSOR curTrackstatus (IN_ContainerName Container.ContainerName%TYPE) IS
    SELECT 
        A.trackstatus 
    FROM 
        A_WIPEQUIPMENT A,
        CONTAINER B 
    WHERE 
        A.containerid=B.containerid 
        AND a.trackstatus = 'INPROCESS' 
        AND b.containername= IN_ContainerName;
    
    CURSOR curSpecName (IN_ContainerName Container.ContainerName%TYPE) IS
    select specname from (
    select g.specname from container a,currentstatus b,workflowstep c,workflow d,workflowstep e,specbase g,spec f
    where a.currentstatusid=b.currentstatusid and b.workflowstepid=c.workflowstepid and c.workflowid=d.workflowid 
    and d.workflowid=e.workflowid and e.specbaseid=g.specbaseid and a.containername=IN_ContainerName and g.specbaseid=f.specbaseid
    and g.specname>'3080' and f.ObjectCategory = 'WIP' 
    order by e.sequence)
    where rownum=1;
    
    --=====================
    -- Local variables
    --=====================
    tInputData TYPE_LotCombine_InputData;
    tServiceData TYPE_LotCombine_ServiceData;
    nCount NUMBER;
    tChildLotData TYPE_LotCombine_Lot;
    --=====================
    -- Pragma Declarations
    --=====================
    eExit EXCEPTION;
    PRAGMA EXCEPTION_INIT (eExit, -20000);
    eAppError EXCEPTION;
    PRAGMA EXCEPTION_INIT (eAppError, -20005);
BEGIN
    --=====================
    -- Save the input variables
    --=====================
    tInputData.MainLotId := MainLotId;
    tInputData.ChildLotIds := TRGUtil.SetCollection(ChildLotIds, ',');
    --=====================
    -- Fetch Main Lot information
    --=====================
    OPEN curLot(tInputData.MainLotId);
    FETCH curLot INTO tServiceData.MainLot;
    CLOSE curLot;
    
    OPEN curTrackstatus(tInputData.MainLotId);
    LOOP
          EXIT WHEN curTrackstatus%NOTFOUND;
          FETCH curTrackstatus
          INTO tServiceData.MainLot.InprocessStatus; 
    END LOOP;
    CLOSE curTrackstatus;
    
    OPEN curSpecName(tInputData.MainLotId);
    LOOP
          EXIT WHEN curSpecName%NOTFOUND;
          FETCH curSpecName
          INTO tServiceData.MainLot.MTFirstStep; 
    END LOOP;
    CLOSE curSpecName;
    
    SELECT
        B.ObjectCategory
        , B.ObjectType
    INTO
        tServiceData.MainLot.SpecObjectCategory
        , tServiceData.MainLot.SpecObjectType
    FROM
        CurrentStatus A
        , Spec B
    WHERE
        A.SpecId = B.SpecId
        AND A.CurrentStatusId = tServiceData.MainLot.CurrentStatusId
    ;
    IF tServiceData.MainLot.SpecObjectCategory = 'WIP' THEN
        SELECT
            B.ProcessSpecName
            , A.ProcessSpecRevision
        INTO
            tServiceData.MainLot.ProcessSpecName
            , tServiceData.MainLot.ProcessSpecRevision
        FROM
            A_ProcessSpec A
            , A_ProcessSpecBase B
        WHERE
            A.ProcessSpecBaseId = B.ProcessSpecBaseId
            AND A.ProcessSpecId = tServiceData.MainLot.ProcessSpecId
        ;

        
        SELECT COUNT(*)
        INTO tServiceData.MainLot.FutureSetupCount 
        FROM (select b.containername,c.holdreasonname 
              from A_LOTFUTUREHOLDHISTORY a, CONTAINER b,HOLDREASON c 
              where a.containerid=b.containerid and a.holdreasonid=c.holdreasonid 
              and a.status != 'CANCELED' and b.containername = tServiceData.MainLot.ContainerName and c.holdreasonname in ('MRB','ENG2')
              union all
              select b.containername,c.holdreasonname 
              from A_LOTFUTUREHOLD a, CONTAINER b,HOLDREASON c 
              where a.containerid=b.containerid and a.holdreasonid=c.holdreasonid 
              and b.containername= tServiceData.MainLot.ContainerName and c.holdreasonname in ('MRB','ENG2'));
              
              
        tServiceData.MainLot.OPN := tServiceData.MainLot.LotOPN;
        tServiceData.MainLot.OPNRevision := tServiceData.MainLot.LotOPNRevision;
    ELSE
        tServiceData.MainLot.OPN := tServiceData.MainLot.ProductOPN;
        tServiceData.MainLot.OPNRevision := tServiceData.MainLot.ProductOPNRevision;
    END IF;
    
    --=====================
    -- Fetch Child Lots information
    --=====================
    tServiceData.ChildLots.DELETE;
    FOR nCount IN 0..tInputData.ChildLotIds.COUNT-1 LOOP
        OPEN curLot(tInputData.ChildLotIds(nCount));
        FETCH curLot INTO tChildLotData;
        CLOSE curLot;
        IF tServiceData.MainLot.SpecObjectCategory = 'WIP' THEN
            IF tChildLotData.ProcessSpecId = tServiceData.MainLot.ProcessSpecId THEN
                tChildLotData.ProcessSpecName := tServiceData.MainLot.ProcessSpecName;
                tChildLotData.ProcessSpecRevision := tServiceData.MainLot.ProcessSpecRevision;
            ELSE
                SELECT
                    B.ProcessSpecName
                    , A.ProcessSpecRevision
                INTO
                    tChildLotData.ProcessSpecName
                    , tChildLotData.ProcessSpecRevision
                FROM
                    A_ProcessSpec A
                    , A_ProcessSpecBase B
                WHERE
                    A.ProcessSpecBaseId = B.ProcessSpecBaseId
                    AND A.ProcessSpecId = tChildLotData.ProcessSpecId
                ;
            END IF;
            
            SELECT COUNT(*)
            INTO tChildLotData.FutureSetupCount 
            FROM (select b.containername,c.holdreasonname 
                  from A_LOTFUTUREHOLDHISTORY a, CONTAINER b,HOLDREASON c 
                  where a.containerid=b.containerid and a.holdreasonid=c.holdreasonid 
                  and a.status != 'CANCELED' and b.containername = tChildLotData.ContainerName and c.holdreasonname in ('MRB','ENG2')
                  union all
                  select b.containername,c.holdreasonname 
                  from A_LOTFUTUREHOLD a, CONTAINER b,HOLDREASON c 
                  where a.containerid=b.containerid and a.holdreasonid=c.holdreasonid 
                  and b.containername= tChildLotData.ContainerName and c.holdreasonname in ('MRB','ENG2'));
                  
            tChildLotData.OPN := tChildLotData.LotOPN;
            tChildLotData.OPNRevision := tChildLotData.LotOPNRevision;
        ELSE
            tChildLotData.OPN := tChildLotData.ProductOPN;
            tChildLotData.OPNRevision := tChildLotData.ProductOPNRevision;
        END IF;
        tServiceData.ChildLots(tServiceData.ChildLots.COUNT) := tChildLotData;
    END LOOP;
    
    --=====================
    -- Verify data
    --=====================
    VerifyAttributes (tInputData, tServiceData);
    --IF tServiceData.MainLot.DateCode IS NOT NULL THEN
    --    VerifyDateCode (tInputData, tServiceData);
    --END IF;
    --=====================
    -- Set the Success Message
    --=====================
    SetAttributes (tInputData, tServiceData);
    tServiceData.NewProductName := REPLACE(tServiceData.NewProductName, ' ', '@@@@@@@@@@');
    SuccessMessage := '0 - ' || tServiceData.NewDateCode || ' ' || tServiceData.NewProductName || ' ' || tServiceData.NewBinNumber;
EXCEPTION
    WHEN eAppError THEN
        RAISE_APPLICATION_ERROR (-20005, SQLERRM);
    WHEN OTHERS THEN
        RAISE_APPLICATION_ERROR (-20005, 'LotCombine.VerifyData2->' || SQLERRM);
END VerifyData2;

--=====================
-- ExecuteData
--=====================
FUNCTION ExecuteData (LotIds IN VARCHAR2)
RETURN VARCHAR2 IS
    --=====================
    -- Input parameter variables
    --=====================

    --=====================
    -- Local variables
    --=====================

    --=====================
    -- Pragma Declarations
    --=====================
    PRAGMA AUTONOMOUS_TRANSACTION;
    eExit EXCEPTION;
    PRAGMA EXCEPTION_INIT (eExit, -20000);
    eAppError EXCEPTION;
    PRAGMA EXCEPTION_INIT (eAppError, -20005);
BEGIN
    --=====================
    -- Call the procedure
    --=====================
    ExecuteData2 (LotIds);
    --=====================
    -- Return success
    --=====================
    RETURN ('0');
EXCEPTION
    WHEN eExit THEN
        ROLLBACK;
        RETURN ('LotCombine.ExecuteData->Exited prematurely');
    WHEN eAppError THEN
        ROLLBACK;
        RETURN (REPLACE(SQLERRM, 'ORA-20005: '));
    WHEN OTHERS THEN
        ROLLBACK;
        RETURN ('LotCombine.ExecuteData->' || SQLERRM);
END ExecuteData;

--=====================
-- ExecuteData2
--=====================
PROCEDURE ExecuteData2 (LotIds IN VARCHAR2)
IS
    --=====================
    -- Input parameter variables
    --=====================

    --=====================
    -- Local variables
    --=====================

    --=====================
    -- Pragma Declarations
    --=====================
    eExit EXCEPTION;
    PRAGMA EXCEPTION_INIT (eExit, -20000);
    eAppError EXCEPTION;
    PRAGMA EXCEPTION_INIT (eAppError, -20005);
BEGIN
    --=====================
    --
    --=====================

    --=====================
    -- Commit the data
    --=====================
    COMMIT;
EXCEPTION
    WHEN eExit THEN
        ROLLBACK;
    WHEN eAppError THEN
        ROLLBACK;
        RAISE_APPLICATION_ERROR (-20005, SQLERRM);
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE_APPLICATION_ERROR (-20005, 'LotCombine.ExecuteData2->' || SQLERRM);
END ExecuteData2;

--=====================
-- GetMainLot
--=====================
FUNCTION GetMainLot (LotIds IN VARCHAR2)
RETURN VARCHAR2 IS
    --=====================
    -- Input parameter variables
    --=====================
    cLotIds TRGUtil.COLLECTION := TRGUtil.SetCollection(LotIds, ',');
    --=====================
    -- Local variables
    --=====================
    vDate A_LotAttributes.DateCode%TYPE;
    vOldestDate A_LotAttributes.DateCode%TYPE;
    vOldestDateCode A_LotAttributes.DateCode%TYPE;
    vMainLot Container.ContainerName%TYPE;
    nCount NUMBER;
    --=====================
    -- Pragma Declarations
    --=====================
    eExit EXCEPTION;
    PRAGMA EXCEPTION_INIT (eExit, -20000);
    eAppError EXCEPTION;
    PRAGMA EXCEPTION_INIT (eAppError, -20005);
BEGIN
    --=====================
    -- Get the lot with the oldest date code
    --=====================
    IF cLotIds.COUNT > 0 THEN
        FOR nCount IN 0..cLotIds.COUNT-1 LOOP
            SELECT
                NVL(B.DateCode, '99991')
            INTO
                vDate
            FROM
                Container A
                , A_LotAttributes B
            WHERE
                A.ContainerId = B.ContainerId
                AND A.ContainerName = cLotIds(nCount)
            ;
            vDate := SUBSTR(vDate, 1, 5);
            IF vMainLot IS NULL OR vDate < vOldestDate THEN
                vMainLot := cLotIds(nCount);
                vOldestDate := vDate;
            END IF;
        END LOOP;
    END IF;
    --=====================
    -- Return the main lot
    --=====================
    RETURN ('0 ' || vMainLot);
EXCEPTION
    WHEN eExit THEN
        RETURN ('LotCombine.GetMainLot->Exited prematurely');
    WHEN eAppError THEN
        RETURN (REPLACE(SQLERRM, 'ORA-20005: '));
    WHEN OTHERS THEN
        RETURN ('LotCombine.GetMainLot->' || SQLERRM);
END GetMainLot;

--=====================
-- VerifyAttributes
--=====================
PROCEDURE VerifyAttributes (InputData IN TYPE_LotCombine_InputData
                            , ServiceData IN TYPE_LotCombine_ServiceData)
IS
    --=====================
    -- Input parameter variables
    --=====================

    --=====================
    -- Local variables
    --=====================
    nCount NUMBER;
    tChildLotData TYPE_LotCombine_Lot;
    --=====================
    -- Pragma Declarations
    --=====================
    eExit EXCEPTION;
    PRAGMA EXCEPTION_INIT (eExit, -20000);
    eAppError EXCEPTION;
    PRAGMA EXCEPTION_INIT (eAppError, -20005);
BEGIN
    --=====================
    -- Verify each Child Lot against the Main Lot
    --=====================
    FOR nCount IN 0..ServiceData.ChildLots.COUNT-1 LOOP
        tChildLotData := ServiceData.ChildLots(nCount);
        --=====================
        -- Compare Product attributes
        --=====================
        IF tChildLotData.ProductId <> ServiceData.MainLot.ProductId THEN
            IF NVL(tChildLotData.Device, '1') <> NVL(ServiceData.MainLot.Device, '1') THEN
                RAISE_APPLICATION_ERROR (-20005, 'LotCombine_E1001 ' || tChildLotData.ContainerName || ' Device');
            END IF;
            IF NVL(tChildLotData.DeviceVersion, '1') <> NVL(ServiceData.MainLot.DeviceVersion, '1') THEN
                RAISE_APPLICATION_ERROR (-20005, 'LotCombine_E1001 ' || tChildLotData.ContainerName || ' Device_Version');
            END IF;
            IF NVL(tChildLotData.PackageType, '1') <> NVL(ServiceData.MainLot.PackageType, '1') THEN
                RAISE_APPLICATION_ERROR (-20005, 'LotCombine_E1001 ' || tChildLotData.ContainerName || ' Package_Type');
            END IF;
            IF NVL(tChildLotData.Leads, '1') <> NVL(ServiceData.MainLot.Leads, '1') THEN
                RAISE_APPLICATION_ERROR (-20005, 'LotCombine_E1001 ' || tChildLotData.ContainerName || ' Leads');
            END IF;
        END IF;
        --=====================
        -- Compare Lot attributes
        --=====================
        IF NVL(tChildLotData.OwnerId, '1') <> NVL(ServiceData.MainLot.OwnerId, '1') THEN
            RAISE_APPLICATION_ERROR (-20005, 'LotCombine_E1001 ' || tChildLotData.ContainerName || ' Owner');
        END IF;
        IF NVL(tChildLotData.LotType, '1') <> NVL(ServiceData.MainLot.LotType, '1') THEN
            RAISE_APPLICATION_ERROR (-20005, 'LotCombine_E1001 ' || tChildLotData.ContainerName || ' LotType');
        END IF;
        --=====================
        -- Check Work Order is Same or not
        -- Deng Chao
        -- 2007-12-07
        --=====================
	      IF NVL(tChildLotData.MfgOrderID, '1') <> NVL(ServiceData.MainLot.MFGORDERID, '1') THEN
	          RAISE_APPLICATION_ERROR (-20005, 'LotCombine_E1001 ' || tChildLotData.ContainerName || ' Work Order');
	      END IF;
        --=====================
        -- Check Substrate Information is same or not
        -- Deng Chao
        -- 2009-04-14
        --=====================
        IF NVL(tChildLotData.SubstrateVersion, '1') <> NVL(ServiceData.MainLot.SubstrateVersion, '1') AND tChildLotData.SpecName = '3060' THEN
	          RAISE_APPLICATION_ERROR (-20005, 'LotCombine_E1001 ' || tChildLotData.ContainerName || ' SubstrateVersion');
	      END IF;
        IF NVL(tChildLotData.StripDrawingVersion, '1') <> NVL(ServiceData.MainLot.StripDrawingVersion, '1') AND tChildLotData.SpecName = '3060' THEN
	          RAISE_APPLICATION_ERROR (-20005, 'LotCombine_E1001 ' || tChildLotData.ContainerName || ' StripDrawingVersion');
	      END IF;
        IF NVL(tChildLotData.SubstrateVendor, '1') <> NVL(ServiceData.MainLot.SubstrateVendor, '1') AND tChildLotData.SpecName = '3060' THEN
	          RAISE_APPLICATION_ERROR (-20005, 'LotCombine_E1001 ' || tChildLotData.ContainerName || ' SubstrateVendor');
	      END IF;
        IF NVL(tChildLotData.LotCategory, '1') <> NVL(ServiceData.MainLot.LotCategory, '1') THEN
            RAISE_APPLICATION_ERROR (-20005, 'LotCombine_E1001 ' || tChildLotData.ContainerName || ' LotCategory');
        END IF;
        IF ServiceData.MainLot.LotCategory = 'M' AND TO_NUMBER(ServiceData.MainLot.SpecName) < 6000 THEN
            IF NVL(tChildLotData.OriginalContainerName, tChildLotData.ContainerName) <> NVL(ServiceData.MainLot.OriginalContainerName, ServiceData.MainLot.ContainerName) THEN
                RAISE_APPLICATION_ERROR (-20005, 'LotCombine_E1001 ' || tChildLotData.ContainerName || ' MotherLot');
            END IF;
        END IF;
        
        --=====================
        -- Check future hold setup or not
        -- Bryan
        -- 2010-03-30
        --=====================
        IF tChildLotData.FutureSetupCount >= 1 
          AND tChildLotData.ContainerName NOT IN ('E0B29MB894.0A','E0B29MB894.0B')
          THEN
	          RAISE_APPLICATION_ERROR (-20005, 'LotCombine_E1011 ' || tChildLotData.ContainerName );
	    END IF;
	    IF ServiceData.MainLot.FutureSetupCount >= 1 THEN
	          RAISE_APPLICATION_ERROR (-20005, 'LotCombine_E1011 ' || ServiceData.MainLot.ContainerName );
	    END IF;
	      
        --=====================
        -- if main lot is inprocess,cannot allow to combine
        -- Bryan
        -- 2010-07-07
        --=====================
        IF ServiceData.MainLot.InprocessStatus = 'INPROCESS' THEN
	        RAISE_APPLICATION_ERROR (-20005, 'LotCombine_E1012 ' || tChildLotData.ContainerName );
        END IF;
        
        --=====================
        -- after MT first step.cannot allow to combine
        -- Bryan
        -- 2010-07-07
        --=====================
        IF TO_NUMBER(ServiceData.MainLot.SpecName) > TO_NUMBER(ServiceData.MainLot.MTFirstStep) 
          AND TO_NUMBER(ServiceData.MainLot.SpecName) < 6000 
          AND ServiceData.MainLot.LotCategory != 'Q' 
          AND ServiceData.MainLot.SpecName != '5010'
          AND ServiceData.MainLot.SpecName != '5011'          
          AND ServiceData.MainLot.ContainerName NOT IN ('M0B21MFF6F.02','M0B21MFF6F.01') THEN
             RAISE_APPLICATION_ERROR (-20005, 'LotCombine_E1013 ' || tChildLotData.ContainerName );
        END IF;
        
        --======================
        -- bryan test
        --======================
        IF ServiceData.MainLot.LotCategory = 'T'THEN
            IF NVL(tChildLotData.OriginalContainerName, tChildLotData.ContainerName) <> NVL(ServiceData.MainLot.OriginalContainerName, ServiceData.MainLot.ContainerName) THEN
                RAISE_APPLICATION_ERROR (-20005, 'LotCombine_E1001 ' || tChildLotData.ContainerName || ' MotherLot');
            END IF;
        END IF;
        IF ServiceData.MainLot.LotCategory = 'C' AND TO_NUMBER(ServiceData.MainLot.SpecName) < 2060 THEN
            IF NVL(tChildLotData.OriginalContainerName, tChildLotData.ContainerName) <> NVL(ServiceData.MainLot.OriginalContainerName, ServiceData.MainLot.ContainerName) THEN
                RAISE_APPLICATION_ERROR (-20005, 'LotCombine_E1001 ' || tChildLotData.ContainerName || ' MotherLot');
            END IF;
        END IF;
        IF ServiceData.MainLot.LotCategory In ('S','SU','SK','ST') AND TO_NUMBER(ServiceData.MainLot.SpecName) < 1030 THEN
            IF NVL(tChildLotData.OriginalContainerName, tChildLotData.ContainerName) <> NVL(ServiceData.MainLot.OriginalContainerName, ServiceData.MainLot.ContainerName) THEN
                RAISE_APPLICATION_ERROR (-20005, 'LotCombine_E1001 ' || tChildLotData.ContainerName || ' MotherLot');
            END IF;
        END IF;
        --=====================
        -- Verify lot attributes
        --=====================
        IF ServiceData.MainLot.DateCode IS NOT NULL AND tChildLotData.DateCode IS NULL THEN
            RAISE_APPLICATION_ERROR (-20005, 'LotCombine_E1002 ' || tChildLotData.ContainerName);
        END IF;
        IF ServiceData.MainLot.DateCode IS NULL AND tChildLotData.DateCode IS NOT NULL THEN
            RAISE_APPLICATION_ERROR (-20005, 'LotCombine_E1003 ' || tChildLotData.ContainerName);
        END IF;
        --=====================
        -- Check only the same datecode can conbime at 3060 step.
        -- Deng Chao, 2009-04-23
        --=====================
        IF ServiceData.MainLot.DateCode <> tChildLotData.DateCode AND tChildLotData.SpecName = '3060' THEN
	          RAISE_APPLICATION_ERROR (-20005, 'LotCombine_E1001 ' || tChildLotData.ContainerName);
	      END IF;
    END LOOP;
EXCEPTION
    WHEN eAppError THEN
        RAISE_APPLICATION_ERROR (-20005, SQLERRM);
    WHEN OTHERS THEN
        RAISE_APPLICATION_ERROR (-20005, 'LotCombine.VerifyAttributes->' || SQLERRM);
END VerifyAttributes;

--=====================
-- VerifyDateCode
--=====================
PROCEDURE VerifyDateCode (InputData IN TYPE_LotCombine_InputData
                          , ServiceData IN TYPE_LotCombine_ServiceData)
IS
    --=====================
    -- Input parameter variables
    --=====================

    --=====================
    -- Local variables
    --=====================
    nCount NUMBER;
    tChildLotData TYPE_LotCombine_Lot;
    vDate VARCHAR2(4);
    nDate NUMBER := 0;
    nOldestDate NUMBER := 0;
    nNewestDate NUMBER := 0;
    vNewestLotId Container.ContainerName%TYPE;
    cDateCodes TRGUtil.COLLECTION;
    nDateCodeCount NUMBER;
    nDateCodeFound NUMBER;
    --=====================
    -- Pragma Declarations
    --=====================
    eExit EXCEPTION;
    PRAGMA EXCEPTION_INIT (eExit, -20000);
    eAppError EXCEPTION;
    PRAGMA EXCEPTION_INIT (eAppError, -20005);
BEGIN
    --=====================
    -- Get the oldest and newest workweek
    --=====================
    vDate := SUBSTR(ServiceData.MainLot.DateCode, 1, 4);
    cDateCodes(cDateCodes.COUNT) := ServiceData.MainLot.DateCode;
    IF vDate >= '0000' AND vDate <= '9953' THEN
        nOldestDate := TO_NUMBER(vDate);
        nNewestDate := TO_NUMBER(vDate);
    END IF;
    FOR nCount IN 0..ServiceData.ChildLots.COUNT-1 LOOP
        tChildLotData := ServiceData.ChildLots(nCount);
        vDate := SUBSTR(tChildLotData.DateCode, 1, 4);
        IF vDate >= '0000' AND vDate <= '9953' THEN
            nDate := TO_NUMBER(vDate);
            IF nDate < nOldestDate THEN
                nOldestDate := nDate;
            ELSIF nDate > nNewestDate THEN
                nNewestDate := nDate;
                vNewestLotId := tChildLotData.ContainerName;
            END IF;
        END IF;
        --=====================
        -- Get distinct Date Codes
        --=====================
        nDateCodeFound := 0;
        FOR nDateCodeCount IN 0..cDateCodes.COUNT-1 LOOP
            IF tChildLotData.DateCode = cDateCodes(nDateCodeCount) THEN
                nDateCodeFound := 1;
            END IF;
        END LOOP;
        IF nDateCodeFound = 0 THEN
            cDateCodes(cDateCodes.COUNT) := tChildLotData.DateCode;
        END IF;
    END LOOP;
    --=====================
    -- Compare the oldest and newest dates
    --=====================
    --IF nNewestDate - nOldestDate > 6 THEN
    --    RAISE_APPLICATION_ERROR (-20005, 'LotCombine_E1005 ' || vNewestLotId);
    --END IF;
    --=====================
    -- Check the number of Date Codes allowed
    --=====================
    --IF cDateCodes.COUNT > 2 THEN
    --    RAISE_APPLICATION_ERROR (-20005, 'LotCombine_E1009 ' || vNewestLotId);
    --END IF;
EXCEPTION
    WHEN eAppError THEN
        RAISE_APPLICATION_ERROR (-20005, SQLERRM);
    WHEN OTHERS THEN
        RAISE_APPLICATION_ERROR (-20005, 'LotCombine.VerifyDateCode->' || SQLERRM);
END VerifyDateCode;

--=====================
-- SetAttributes
--=====================
PROCEDURE SetAttributes (InputData IN TYPE_LotCombine_InputData
                         , ServiceData IN OUT TYPE_LotCombine_ServiceData)
IS
    --=====================
    -- Local variables
    --=====================
    nCount NUMBER;
    tChildLotData TYPE_LotCombine_Lot;
    nCount2 NUMBER;
    tChildLotData2 TYPE_LotCombine_Lot;
    --=====================
    -- Pragma Declarations
    --=====================
    eExit EXCEPTION;
    PRAGMA EXCEPTION_INIT (eExit, -20000);
    eAppError EXCEPTION;
    PRAGMA EXCEPTION_INIT (eAppError, -20005);
BEGIN
    FOR nCount IN 0..ServiceData.ChildLots.COUNT-1 LOOP
        tChildLotData := ServiceData.ChildLots(nCount);
        --=====================
        -- Check for TMP
        --=====================
        IF ServiceData.MainLot.SpecObjectType IN ('TEST', 'TESTFE', 'TESTBE', 'TESTSTORE') THEN
            --=====================
            -- Set the New Bin Number if a bigger number is found
            --=====================
            IF tChildLotData.BinNumber IS NOT NULL THEN
                IF ServiceData.NewBinNumber = '0' THEN
                    ServiceData.NewBinNumber := NVL(ServiceData.MainLot.BinNumber, '0');
                END IF;
                IF tChildLotData.BinNumber > ServiceData.NewBinNumber THEN
                    ServiceData.NewBinNumber := tChildLotData.BinNumber;
                    SELECT
                        B.ProductName
                    INTO
                        ServiceData.NewProductName
                    FROM
                        Product A
                        , ProductBase B
                    WHERE
                        A.ProductBaseId = B.ProductBaseId
                        AND A.ProductId = tChildLotData.ProductId
                    ;
                END IF;
            END IF;
        END IF;
    END LOOP;
EXCEPTION
    WHEN eAppError THEN
        RAISE_APPLICATION_ERROR (-20005, SQLERRM);
    WHEN OTHERS THEN
        RAISE_APPLICATION_ERROR (-20005, 'LotCombine.SetAttributes->' || SQLERRM);
END SetAttributes;

--===========================================================================
-- End of package body
--===========================================================================
END;
/
